<?php
/**
 * 
 * @author Daitk
 *
 */
class Model_Baocao {
	protected $db;
	public function __construct() {
		$this->db = Zend_Registry::get ( "db" );
	}
	/**
	 * - NGÂN SÁCH TRẠM Y TẾ
	 *
	 * @param Number $thongtincoquanId        	
	 * @param Number $namhoatdong        	
	 */
	public function getdanhsachuser() {
		$str_query =  "SELECT id, name,phone,roles,luong,ngaynghi,trocap,ghichu,luongngay,tamung, (luong - tamung - (ngaynghi*luongngay) + trocap ) as thuclinh  FROM user where id != 1";
		$query = $this->db->query ( $str_query );
		return $query->fetchAll ();
	}
	public function getdanhsachuserct($id) {
		$str_query =  "SELECT id, name,phone,roles,luong,ngaynghi,trocap,ghichu,luongngay,tamung, (luong - tamung - (ngaynghi*luongngay) + trocap ) as thuclinh  FROM user where id != 1 and id = $id ";
		$query = $this->db->query ( $str_query );
		return $query->fetchAll ();
	}
	public function getdanhsachno() {
		$str_query =  "SELECT id,customerName,(DATE_FORMAT(created,'%d/%m/%Y - %H:%i:%p')) AS created,createdBy,fee,total,debit,note,prepaid,promotion,payment,checkmo,customerPassport FROM orders where debit = 0";
		$query = $this->db->query ( $str_query );
		return $query->fetchAll ();
	}
	
	public function getdanhsachnoct($cmt) {
		$str_query =  "SELECT id,customerName,(DATE_FORMAT(created,'%d/%m/%Y - %H:%i:%p')) AS created,createdBy,fee,total,debit,note,prepaid,promotion,payment,checkmo,customerPassport FROM orders where debit = 0 and customerPassport = $cmt";
		$query = $this->db->query ( $str_query );
		return $query->fetchAll ();
	}
	public function getorders($startday,$endday) {
		if ($startday != '' && $endday != '')
		{
			$where = "created BETWEEN  '$startday' AND '$endday' ";
		}
		elseif ($startday == '' || $endday == '') {
			if ($startday != ''){
				$where = " created => '$startday' ";
				}
			if ($endday != ''){
				$where = " created <= '$endday'";
				}
		}
		$str_query =  "SELECT id,customerName,createdBy,(DATE_FORMAT(created,'%d/%m/%Y - %H:%i:%p')) AS created,fee,total,debit,note,prepaid,promotion,payment,checkmo,customerPassport 
		FROM orders 
        where $where ";
		$query = $this->db->query ( $str_query );
		return $query->fetchAll ();
	}
	
	public function getbaocaonhapkho($startday,$endday) {
	    if ($startday != '' && $endday != '')
		{
			$where = "created BETWEEN  '$startday' AND '$endday' ";
		}
		elseif ($startday == '' || $endday == '') {
			if ($startday != ''){
				$where = " created => '$startday' ";
				}
			if ($endday != ''){
				$where = " created <= '$endday'";
				}
		}
		$str_query = " SELECT *
          FROM store
          INNER JOIN storeproduct
          ON store.id = storeproduct.storeId where $where";
		$query = $this->db->query ( $str_query );
		return $query->fetchAll ();
	}
	
	
	public function getbaocaonhapkhos($startday,$endday,$sort, $order, $offset, $rows) {
	   $result = array ();
	    if ($startday != '' && $endday != '')
		{
			$where = "created BETWEEN  '$startday' AND '$endday' ";
		}
		elseif ($startday == '' || $endday == '') {
			if ($startday != ''){
				$where = " created => '$startday' ";
				}
			if ($endday != ''){
				$where = " created <= '$endday'";
				}
		}
		// Lay tong so ban ghi
		
		$query = $this->db->query ( "SELECT COUNT(*) AS Total FROM store INNER JOIN storeproduct ON store.id = storeproduct.storeId  WHERE  $where " );
		$row = $query->fetchAll ();
		$result ["total"] = $row [0] ['Total'];
		$str_query = " SELECT * FROM store INNER JOIN storeproduct ON store.id = storeproduct.storeId where $where ORDER BY $sort $order LIMIT $offset, $rows";
		$query = $this->db->query ( $str_query );
	    $result ['rows'] = $query->fetchAll ();
		return $result;
	}
	
	
	public function getbaocaoxuatkhos($startday,$endday,$sort, $order, $offset, $rows) {
	   $result = array ();
	    if ($startday != '' && $endday != '')
		{
			$where = "created BETWEEN  '$startday' AND '$endday' ";
		}
		elseif ($startday == '' || $endday == '') {
			if ($startday != ''){
				$where = " created => '$startday' ";
				}
			if ($endday != ''){
				$where = " created <= '$endday'";
				}
		}
		// Lay tong so ban ghi
		
		$query = $this->db->query ( "SELECT COUNT(*) AS Total FROM orders INNER JOIN orderproduct  WHERE  $where and orderproduct.productType != 'Room' and orderproduct.productType != 'RoomType' and payment = 1 " );
		$row = $query->fetchAll ();
		$result ["total"] = $row [0] ['Total'];
		$str_query = " SELECT orderproduct.id as id, createdBy,price,quantity,orderproduct.total as totals,created,productName
          FROM orders
          INNER JOIN orderproduct
          ON orders.id = orderproduct.orderId where $where and orderproduct.productType != 'Room' and orderproduct.productType != 'RoomType' and payment = 1 ";
		$query = $this->db->query ( $str_query );
	    $result ['rows'] = $query->fetchAll ();
		return $result;
	}
	
	
	public function getbaocaoxuatkho($startday,$endday) {
	    if ($startday != '' && $endday != '')
		{
			$where = "created BETWEEN  '$startday' AND '$endday' ";
		}
		elseif ($startday == '' || $endday == '') {
			if ($startday != ''){
				$where = " created => '$startday' ";
				}
			if ($endday != ''){
				$where = " created <= '$endday'";
				}
		}
		
		$str_query = " SELECT orderproduct.id as id, createdBy,price,quantity,orderproduct.total as totals,created,productName
          FROM orders
          INNER JOIN orderproduct
          ON orders.id = orderproduct.orderId where $where and orderproduct.productType != 'Room' and orderproduct.productType != 'RoomType' and payment = 1 ";
		$query = $this->db->query ( $str_query );
		return $query->fetchAll ();
	}
	
	
	public function getbaocaodoanhthu($startday,$endday) {
	if ($startday != '' && $endday != '')
		{
			$where = "created BETWEEN  '$startday' AND '$endday' ";
		}
		elseif ($startday == '' || $endday == '') {
			if ($startday != ''){
				$where = " created > '$startday' ";
				}
			if ($endday != ''){
				$where = " created <= '$endday'";
				}
		}
		$str_query = " SELECT sum(prepaid+total) as tongthu 		
		FROM orders where $where and payment = 1";
		$query = $this->db->query ( $str_query );
		return $query->fetchAll ();
	}
	public function getdoanhchi($startday,$endday) {
	    if ($startday != '' && $endday != '')
		{
			$where = "created BETWEEN  '$startday' AND '$endday' ";
		}
		elseif ($startday == '' || $endday == '') {
			if ($startday != ''){
				$where = " created > '$startday' ";
				}
			if ($endday != ''){
				$where = " created <= '$endday'";
				}
		}
		$str_query = " SELECT sum(total) as tongchi		
		FROM store where $where ";
		$query = $this->db->query ( $str_query );
		return $query->fetchAll ();
	}
	
}